const pool = require("../db/conn");
const countControllers = require("../controllers/countControllers");

exports.selectAuthList = async function() {
    let sql =  "SELECT"+
                "	rc_department.department_id, "+
                "	rc_department.department_name "+
                "FROM "+
                "	rc_department "+
                "WHERE "+
                "	rc_department.department_is_delete = 0 ";
    let conn = await pool.getConnection();
    let [result] = await conn.execute(sql);
    conn.release();
    return result
}

exports.selectList = async function (pageno,pagesize,query) {
    let sql =  "SELECT"+
                "	rc_department.department_id, "+
                "	rc_department.department_name, "+
                "	COUNT(rc_major.major_id) AS major_count, "+
                "	COUNT(rc_teacher.teacher_id) AS teacher_count "+
                "FROM "+
                "	rc_department "+
                "	LEFT JOIN "+
                "	rc_major "+
                "	ON "+
                "		rc_department.department_id = rc_major.major_department_id "+
                "	LEFT JOIN "+
                "	rc_teacher "+
                "	ON "+
                "		rc_department.department_id = rc_teacher.teacher_department_id "+
                "WHERE "+
                "	rc_department.department_is_delete = 0 ";
    let params = [];
    if(query!=null&&query!=""){
        sql += "AND rc_department.department_name like ? "
        params.push( `%${query}%` );
    }
    sql += "GROUP BY rc_department.department_id,  rc_department.department_name ";

    let data = await countControllers.selectCount( sql,params );
    
    sql += "LIMIT ?, ?";
    let start = ((pageno-1)*pagesize)+"";
    let size = pagesize;
    
    params.push(start,size);
    let conn = await pool.getConnection();
    let [result] = await conn.execute(sql,params);
    conn.release();

    data.pageNo = pageno*1
    data.pageSize = pagesize*1;
    data.list = result;
    
    return data;
}

exports.selectById = async function (department_id) {
    let sql =  "SELECT"+
                "	rc_department.department_id, "+
                "	rc_department.department_name, "+
                "	rc_department.department_is_delete "+
                "FROM "+
                "	rc_department "+
                "WHERE "+
                "	rc_department.department_id = ? ";

    let conn = await pool.getConnection();
    let [result] = await conn.execute(sql,[department_id]);
    conn.release();
    
    return result[0];
}

exports.selectByNameAndCondition = async function (department_name,department_id,department_is_delete) {
    let sql =  "SELECT"+
                "	rc_department.department_id, "+
                "	rc_department.department_name, "+
                "	rc_department.department_is_delete "+
                "FROM "+
                "	rc_department "+
                "WHERE "+
                "	rc_department.department_name = ? ";

    let params = [department_name];
    if(department_id!=null&&department_id!=""){
        sql += "AND rc_department.department_id != ? ";
        params.push( department_id );
    }
    if(department_is_delete!=null&&department_is_delete!=""){
        sql += "AND rc_department.department_is_delete = ? ";
        params.push( department_is_delete );
    }

    let conn = await pool.getConnection();
    let [result] = await conn.execute(sql,params);
    conn.release();
    
    return result[0];
}

exports.insert = async function (department_name,state=0) {
    let sql =  "insert into rc_department (department_name,department_is_delete) values (?,?)";

    let conn = await pool.getConnection();
    let [{changedRows}] = await conn.execute(sql,[department_name,state]);
    conn.release();
    
    return changedRows!=0;
}

exports.updateByDid = async function (department_name,department_id,state=0) {
    let sql =  "UPDATE rc_department SET department_name = ? , department_is_delete = ? WHERE department_id = ?";

    let conn = await pool.getConnection();
    let [{changedRows}] = await conn.execute(sql,[department_name,state,department_id]);
    conn.release();
    
    return changedRows!=0;
}

exports.deleteByDid = async function (department_id) {
    let sql =  "UPDATE rc_department SET department_is_delete = 1 WHERE department_id = ?";

    let conn = await pool.getConnection();
    let [{changedRows}] = await conn.execute(sql,[department_id]);
    conn.release();
    
    return changedRows!=0;
}



